#from pycube.core.urls.response import HttpResponse
from pycube.core.sql.model import column
from pycube.core.sql.types import *
import hashlib

def core_setup(psql):
    
    #Registering user table
    psql.register('users',(        
           column('id',       int8, autoSequence = True, primary_key = True, notnull = True, autoIndex = True ),        
           column('active',   bool, default = False),
           column('hash',     varchar(100)),            
           column('login',    varchar(100), notnull = True),
           column('pass',     varchar(100), notnull = True),
           column('email',    varchar(100) ),
           column('group',    int8, notnull = True, default = 1),
           column('reg_date', datetime, default = 'now'),
#           column('birthday', date),
#           column('real_name',varchar(200)),
#           column('sex',      bool , default  = True),
#           column('icq',      varchar(12)),
#           column('skype',    varchar(50)),
#           column('phone',    varchar(100)),
#           column('geo',    int8, autoIndex = True),
#           column('about',    varchar(500)),
#           column('avatar',    varchar(500)),
           column('last_action',  datetime, default = 'now'),            
           column('location',  varchar(500), default = '/'),
           column('permissions',  varchar())
    ))  
    
    psql.register('session',(
           column('id',       int8, autoSequence = True, primary_key = True, notnull = True, autoIndex = True ),
           column('name',    varchar(500)),
           column('expire', datetime, default = 'now'),
           column('save',     bool, default = False),                
           column('clone',     bool, default = False),
           column('last_action', datetime, default = 'now'),
           column('user_id',     int8, autoIndex = True),
    
    )) 
    
    psql.register('groups',(
           column('id',       int8, autoSequence = True, primary_key = True, notnull = True, autoIndex = True ),
           column('title',    varchar(500)),
           column('name', varchar(500)),
           column('data',    varchar())
    )) 
    psql.forein_key('users','group','groups','id','cascade')
    psql.forein_key('session','user_id','users','id','cascade','cascade')
    
    
    
    '''
    Creating default user and core groups
    '''
    data = psql.select('SELECT id, name FROM groups WHERE name = %s OR name = %s', ('root', 'guest'),fetchall = True)
    if data:
        iHave = []
        for id, name in data:
            iHave.append(name)
           
        
        if 'guest' not in iHave:
            new_group_id =  psql.seq('groups_id_sequence')
            
            
            psql.insert('groups', { 'id' : new_group_id, 'title' : 'Guest users', 'name' : 'guest' })
                        
            psql.commit()
         
        if 'root' not in iHave:
            new_root_id = psql.seq('groups_id_sequence')            
            
            
            psql.insert('groups', { 'id' : new_root_id , 'title' : 'Root user', 'name' : 'root' })
            psql.update('users', {'group' : new_root_id }, "WHERE login = 'root'")
            psql.commit()
            
        
            
    else:
        new_root_id = psql.seq('groups_id_sequence')
        new_group_id =  psql.seq('groups_id_sequence')
        psql.insert('groups', { 'id' : new_root_id, 'title' : 'Root user', 'name' : 'root' })
        
        psql.insert('groups', { 'id' : new_group_id, 'title' : 'Guest users', 'name' : 'guest' }) 
        
        psql.commit()    
        
    
    data = psql.select('SELECT id FROM users WHERE login = %s', 'root',fetchone = True)
    if not data:
        
        data = psql.select('SELECT id FROM groups WHERE name = %s','root', fetchone = True)
        root_group_id = data[0]
        
        psql.insert('users',{
        'active' : True,
        'login'  : 'root',
        'pass'   : hashlib.sha1("123").hexdigest(),
        'group'  : root_group_id,
        'email'  : 'root@localhost'
                             
        })
        psql.commit()
